Todays class Document(07/01/2019)

Topics covered in production support:

1.work request(WR)
2.Incident Request(IR)
3.Problem Request(PR)
4.performance tuning

client name : BNP
Application : Sharekhan
Objective   : online trading

Sharekhan have 2 types of customers

1.Retail client
2.Institutional Client


Retail Client:

Suppose the end user is having money in his account and he is doing buy and
sell by himself then they are called as retail client

Institutional Client:

Suppose if there is institutions like LIC,HDFC,Kotak and they are doing trading for 
their clients they are called as institutional client

work request comming from Institutional client of Sharekhan:

Requirement of bulk order form:

Institutional client have asked one requirement that instead of using NEW ORDER FORM
create and give me one BULK ORDER FORM in which we have to place bulk orders.

These are the steps to be followed:

1.In this designing is done by Business Analyst
2.Page is developed by Java Developer
3.Table structure is given by data modeller
4.Now work request starts here as PLSQL developer for production support team

                                BULK ORDER FORM

Exchange      Script      Order_type    Quantity     Market Price  Lowest Price 

NSE	      TCS          BUY            20             100           98
BSE           WIPRO        SELL           40             200          195
NSE           DLF          BUY            100             80           76

                                      TRADE NOW

5.when trade now button is clicked,stored procedure name sp_trade_now procedure is called 
and data is inserted in t_orders table

6.Impact analysis document should be prepared
  Detailed designed document should be prepared
  Test case document should be prepared
  coding
  Unit testing /Test result document
  Release notes should be prepared
  CVS - check in has to be made
  Release Manager-mail to be sent
support testing environment process
DEV
DIT
SIT
UAT
PROD

INCIDENT REQUEST:

Interview Question:

what Incident request recently you have faced?
 
 One of the institutional client of sharekhan has raised incident that if i am placing any orders
using trade now button its taking 4 hours for processing

"PLACE ORDER SUCCESSFULLY" is comming after 4 hours.

step 1:

this incident request will be raised by L1 team through JIRA tool,priority will be set by them and 
escalate to L2 team

step 2 :

In L2 team they will analyse which table has been locked,using the code

    select B.owner,B.Object_name,A.Oracle_username,
    A.os_username,A.session_id,
    from v$ locked_object A,All_objects B
    where A.object_Id = B.object_Id

using this they have analysed that tables have been locked

TABLE LOCK:
 
when one user is using one session(cmd prompt) consider there is one table name t1 in which there is
one column named A and in that 4 records are inserted

desc t1;
select * from t1;

output

1
2
4
5

4 rows selected

now he is updating one record using update command

update t1 set a = 6;

output

1 row updated message has came

similarly one more user is opening another session(cmd prompt) and doing the same process in the same 
table he can't do update 

owner     table    session

 HR         T1        17
 HR         T1        20

table has been locked.the table lock can  be released using COMMIT .

If we give commit that row will be updated

so now L2 team has analysed that session to be killed

L2 will analyse and inform without knowing to the customer kill the session

Step 3 : 

Now L2 team have analysed and escalate to L3 for coding

here they will write coding as

        alter system kill session '20,1234#' 

in short

 L1 - help desk - incident raise - jira - priority set - run book - escalate to L2
 L2 - analyse - session kill
 L3 - coding - fix the bug 


Interview Question

what last faced problem request?

what we have discused in incident request,the same problem repeatedly raised by so many customers
is called as problem request.

when the same complaint raises from the customer L1 team directly asks the L2 team to give
permanent solution

PR request is given by L1 team,now L2 team will analyse and send the report to java developers that
we are going to create one XML file with stored procedure name sp_bulk_orders and whenever this 
procedure is called instead of calling sp_trade_now ,sp_bulk_orders will be called and datas are 
inserted into table in_orders.




CREATE TABLE T_XML_ORDERS(ORDERS XMLTYPE);

CREATE OR REPLACE PROCEDURE SP_BULK_ORDERS
(
IN_ORDERS IN CLOB,
OUT_ORDER_DETAILS OUT SYS_REFCURSOR
)
AS
BEGIN

INSERT INTO T_XML_ORDERS VALUES (XMLTYPE(IN_ORDERS));

COMMIT;

OPEN OUT_ORDER_DETAILS FOR 
SELECT extract(value(d), '//EXCHANGE/text()').getStringVal() AS EXCHANGE,
       extract(value(d), '//ORDER_ID/text()').getNumberVal() AS ORDER_ID
FROM   T_XML_ORDERS x,
       table(xmlsequence(extract(x.ORDERS, '/ROWSET/ROW'))) d;


END;
/


by using this code instead of calling the sp_trade_now 1000 times this procedure is called once and all
1000 records are executed in one single row.

for ex

select
dbms_xmlgen.getdata('select first_name,salary from employees')
from dual;

all 107 records will be displayed under one single row.

Hence L3 team will prepare xml coding and ask java developer to give in xml format


Performance Tuning:

interview question?

recently what issue you have faced in performance tuning?

when i received one problem request like ,while clicking trade order button and placing orders its taking
4 hours to complete the task "order placed successfully"

by writing XML code i have solved the problem and now all 1000 records are fetched within 1 sec.so 
performace has been improved and time is saved and fast response and this is the permanent solution
i have given for the problem raised.

performace tuning is done by production support.